package com.authine.cloudpivot.ext.utils;

import cn.hutool.core.map.MapBuilder;
import com.alibaba.cola.dto.SingleResponse;
import com.authine.cloudpivot.ext.dto.CustomerStatistics.CustomerStatisticResultDto;
import com.authine.cloudpivot.ext.dto.GoodTradeStatisticInfoDto;
import com.authine.cloudpivot.ext.dto.InternalVendorQuotationVO;
import com.authine.cloudpivot.ext.dto.VendorScoreStatistics.VendorScoreDto;
import com.authine.cloudpivot.ext.dto.VendorScoreStatistics.VendorScoreQuotationDto;
import com.authine.cloudpivot.ext.dto.VendorStatsticsDto;
import com.authine.cloudpivot.ext.dto.VendorTradeNumStastics.VendorOrderTradeReturnStastics;
import com.authine.cloudpivot.ext.dto.VendorTradeNumStastics.VendorOrderTradeStastics;
import com.authine.cloudpivot.ext.dto.VendorTradeNumStastics.VendorQuotationTradeStastics;
import com.authine.cloudpivot.ext.dto.vendorStastics.*;
import com.authine.cloudpivot.ext.utils.convert.BOConvert;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import com.authine.mvp.app.launcher.dto.bo.response.BOList;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class VendorStasticsJdbcUtil {
    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    /**
     *
     * @param vendor
     * @return 获取供应商上传商品数 品牌数量
     */
    public static List<VendorGood> queryGoodCountGroupByVendor(VendorStatsticsDto vendor){

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcount(good.id) goodNum,\n" +
                "\tcount(DISTINCT good.brand) brandNum,\n" +
                "\tgood.vendor_id vendor_id\n" +
                "\n" +
                "FROM\n" +
                "\tcommoditymaterials.i_commoditymaterials_t_goodses good\n" +
                "\n" +
                "WHERE\n" +
                "\tgood.deleted = 0 \n" +
                "\tand good.vendor_id IS NOT NULL \n" +
                "\tand good.vendor_id != ''\n"
                );
        Map<String,Object> param = new HashMap<>();
        queryVendorByCondition(vendor,sb);
        sb.append("GROUP BY good.vendor_id ");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorGood>(VendorGood.class));
    }

    private static void queryVendorByCondition(VendorStatsticsDto vendor, StringBuffer sb) {
        if (StringUtils.isNotBlank(vendor.getStartTime())){
            sb.append(" and good.createdTime >= '" + vendor.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(vendor.getEndTime())){
            sb.append(" and good.createdTime <= '" + vendor.getEndTime() + "' ");
        }
    }


    public static List<VendorGood> queryVendorQuotationGoods(VendorStatsticsDto vendor) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcount(good.id) goodCount,\n" +
                "\tcount(DISTINCT good.brand) brandCount,\n" +
                "\tgood.vendor_id\n" +
                "\n" +
                "FROM\n" +
                "\tcommoditymaterials.i_commoditymaterials_t_goodses good\n" +
                "\n" +
                "WHERE\n" +
                "\tgood.deleted = 0 \n" +
                "\tand good.vendor_id IS NOT NULL \n" +
                "\tand good.vendor_id != ''\n"
        );
        Map<String,Object> param = new HashMap<>();
        queryVendorByCondition(vendor,sb);
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(vendor.getVendorOrganizationName())){
            sb.append(" and good.org_fullname like :org_fullname ");
            param.put("org_fullname", Utils.likeVal(vendor.getVendorOrganizationName()));
        }
        sb.append("GROUP BY good.vendor_id ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorGood>(VendorGood.class));

    }

    public static List<VendorQuotationGoods> queryVendortQuotationGoods(VendorStatsticsDto vendor) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tvendor_organization_id vendor_id,\n" +
                "\tcycs,\n" +
                "\tzbcs,\n" +
                "\tbjcs,\n" +
                "\tCONCAT( ROUND( bjcs / cycs * 100, 1 ), '%' ) bjl,\n" +
                "\tCONCAT( ROUND( zbcs / cycs * 100, 1 ), '%' ) zdl \n" +
                "FROM\n" +
                "\t(\n" +
                "\tSELECT\n" +
                "\t\tvendor_organization_id,\n" +
                "\t\tSUM(\n" +
                "\t\tIF\n" +
                "\t\t\t(\n" +
                "\t\t\t\tgood.quotation_goods_status = '3' \n" +
                "\t\t\t\tOR good.quotation_goods_status = '4' \n" +
                "\t\t\t\tOR good.quotation_goods_status = '5' \n" +
                "\t\t\t\tOR good.quotation_goods_status = '6' \n" +
                "\t\t\t\tOR good.quote_count = '7',\n" +
                "\t\t\t\txjsp.quote_count,\n" +
                "\t\t\t\t0 \n" +
                "\t\t\t)) AS bjcs,\n" +
                "\t\tsum( xjsp.partake_count ) cycs,\n" +
                "\t\tSUM(\n" +
                "\t\tIF\n" +
                "\t\t( good.quotation_goods_status = '5', 1, 0 )) AS zbcs \n" +
                "\tFROM\n" +
                "\t\tsourcingcenter.i_sourcingcenter_t_quotation_goods good\n" +
                "\t\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods xjsp ON good.inquiry_goods_id = xjsp.id \n" +
                "\t\tAND xjsp.deleted = 0 \n" +
                "\tWHERE\n" +
                "\t\tgood.deleted = 0 ");
        Map<String,Object> param = new HashMap<>();
        queryVendorByCondition(vendor,sb);

        if (org.apache.commons.lang3.StringUtils.isNotEmpty(vendor.getVendorOrganizationName())){
            sb.append (" and good.vendor_organization_name like  '%"+ vendor.getVendorOrganizationName()+"%'");
        }

        sb.append("GROUP BY good.vendor_organization_id ) a");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorQuotationGoods>(VendorQuotationGoods.class));
    }

    public static List<VendorOrderStatistics> queryVendorOrderStatsticsList(VendorStatsticsDto vendor) {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcount( 1 ) zdcs,\n" +
                "\tsum(amount)zdje,\n" +
                "\tSUM(\n" +
                "\tIF\n" +
                "\t( HOUR ( timediff( place_order_time, createdTime ))>= 12, 1, 0 )) AS wjds,\n" +
                "\tgood.organization_id vendorId \n" +
                "FROM\n" +
                "\tordercenter.i_ordercenter_t_order_vendor good where 1=1 ");
        queryVendorByCondition(vendor,sb);

        if (org.apache.commons.lang3.StringUtils.isNotEmpty(vendor.getVendorOrganizationName())){
            sb.append (" and good.organization_name like  '%"+ vendor.getVendorOrganizationName()+"%'");
        }
        sb.append("GROUP BY good.organization_id ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorOrderStatistics>(VendorOrderStatistics.class));
    }
    public static int queryAllVendorCount(VendorStatsticsDto vendor) {

        MapBuilder<String, Object> param = MapBuilder.create(new HashMap<>(32));
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_VENDOR_ADMIN%' and deleted =0 ");
        if (StringUtils.isNotBlank(vendor.getVendorOrganizationName())){
            sb.append(" and name like :name ");
            param.put("name","%" + vendor.getVendorOrganizationName() + "%");
        }
        if (StringUtils.isNotBlank(vendor.getVendorIds())){
            sb.append(" and id in ('"+ vendor.getVendorIds()+"') ");
        }
        NamedParameterJdbcTemplate nameParameterJdbcTemplate = ApplicationContextUtils.getBean(NamedParameterJdbcTemplate.class);
        return nameParameterJdbcTemplate.queryForObject(sb.toString(), param.build(), Integer.class);

    }

    public static List<Map<String,Object>> queryAllVendor(VendorStatsticsDto vendor,Integer start,Integer size) {
        MapBuilder<String, Object> param = MapBuilder.create(new HashMap<>());
        StringBuffer sb = new StringBuffer();
        sb.append("select * from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_VENDOR_ADMIN%' and deleted =0 ");
        if (StringUtils.isNotBlank(vendor.getVendorOrganizationName())){
            sb.append(" and name like :name ");
            param.put("name","%" + vendor.getVendorOrganizationName() + "%");
        }
        if (StringUtils.isNotBlank(vendor.getVendorIds())){
            sb.append(" and id in ('"+ vendor.getVendorIds()+"') ");
        }

        if (start != null && size != null){
            sb.append( " limit "+start +"," + size);
        }
        List<Map<String, Object>> maps = JdbcTemplateUtils.queryForList(sb.toString(), param.build());
        return maps;
    }

    public static List<VendorPlateUser> queryAllPlateUserOfVendor() {
        StringBuffer sb = new StringBuffer();
        sb.append("select organization_id vendor_id,GROUP_CONCAT(member_id) memberId\n" +
                "from operation1center.i_operation1center_t_platform_grant_user where 1=1 and deleted =0  group by organization_id");
        log.info("sql:"+sb.toString());
        List<VendorPlateUser> vendorPlateUsers = jdbcTemplate.query(sb.toString(), new Object[]{}, new BeanPropertyRowMapper<VendorPlateUser>(VendorPlateUser.class));
        log.info("vendorPlateUsers:{}",vendorPlateUsers);
        return vendorPlateUsers;

    }



    public static List<VendorQuotationGoodThirtySix> queryVendortQuotationGoodsThirtySix(VendorStatsticsDto vendor) {
        StringBuffer sb = new StringBuffer();
        sb.append("select vendor_id,\n" +
                "CONCAT( ROUND( quote_count / total_quote_count * 100, 1 ), '%' ) ThirtySixBjl\n" +
                "from(\n" +
                "\n" +
                "SELECT\n" +
                "\tvendor_organization_id vendor_id,\n" +
                "\tSUM(\n" +
                "\tIF\n" +
                "\t( good.quotation_goods_status = '3' OR good.quotation_goods_status = '4', 1, 0 )) AS quote_count,\n" +
                "\tSUM(\n" +
                "\tIF\n" +
                "\t\t(\n" +
                "\t\t\tgood.quotation_goods_status = '3' \n" +
                "\t\t\tOR good.quotation_goods_status = '4' \n" +
                "\t\t\tOR good.quotation_goods_status = '5' \n" +
                "\t\t\tOR good.quotation_goods_status = '6' \n" +
                "\t\t\tOR good.quote_count = '7',\n" +
                "\t\t\t1,\n" +
                "\t\t\t0 \n" +
                "\t\t)) AS total_quote_count\n" +
                "\t\n" +
                "FROM\n" +
                "\tsourcingcenter.i_sourcingcenter_t_quotation_goods good \n" +
                "WHERE\n" +
                "\tgood.deleted = 0 \n" +
                "\tAND timestampdiff( HOUR, good.createdTime, good.modifiedTime )<= 36 ");
        queryVendorByCondition(vendor,sb);

        if (org.apache.commons.lang3.StringUtils.isNotEmpty(vendor.getVendorOrganizationName())){
            sb.append (" and good.vendor_organization_name like  '%"+ vendor.getVendorOrganizationName()+"%'");
        }
        sb.append(" GROUP BY good.vendor_organization_id ) a");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorQuotationGoodThirtySix>(VendorQuotationGoodThirtySix.class));

    }



    public static List<Map<String,Object>> queryGoodPurchaseGroupBy(List<String> goods){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tSUM( IFNULL(num,0) ) quotationNumber,\n" +
                "\tsum( IFNULL(total,0) ) quotationTotal, \n" +
                "\tname\n" +
                "FROM\n" +
                "\tcommoditymaterials.i_commoditymaterials_t_goods_purchase \n" +
                "WHERE\n" +
                "\tname IN (?) \n" +
                "GROUP BY\n" +
                "\tname");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString(), goods);
        return  maps;

    }

    public static List<Map<String,Object>> queryVendorOrder(List<String> goods){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tSUM( IFNULL(quantity,0) ) orderNumber,\n" +
                "\tsum( IFNULL(total,0) ) orderTotal, \n" +
                "\tname\n" +
                "FROM\n" +
                "\tordercenter.i_ordercenter_t_order_vendor_goods \n" +
                "WHERE\n" +
                "\tname IN (?) \n" +
                "GROUP BY\n" +
                "\tgoods_id");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString(), goods);
        return  maps;

    }


    public static List<VendorQuotationTradeStastics> queryQuotationStatistic(VendorStatsticsDto vendor, StringBuffer  sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcount(1) AS goodNum,\n" +
                "\tSUM(IFNULL(amount,0) )amount ,\n" +
                "\tvendor_organization_name \n" +
                "FROM\n" +
                "\tsourcingcenter.i_sourcingcenter_t_quotation_goods \n" +
                "WHERE\n" +
                "\tquotation_goods_status IN ( 3, 4, 5, 6 ) \n" +
                "\tAND (vendor_organization_name IS NOT NULL and vendor_organization_name !='')\n");
        if (StringUtils.isNotBlank(vendor.getVendorOrganizationName())){
            sb.append(" and name like '%" +vendor.getVendorOrganizationName() + "%'");
        }
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and name in ('"+ sbNames+"') ");
        }
        if (StringUtils.isNotBlank(vendor.getStartTime())){
            sb.append(" and createdTime >= '"+vendor.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(vendor.getEndTime())){
            sb.append(" and createdTime <= '"+vendor.getEndTime()+"'");
        }

        sb.append("GROUP BY vendor_organization_name;");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorQuotationTradeStastics>(VendorQuotationTradeStastics.class));




    }

    public static List<VendorOrderTradeStastics> queryVendorOrderTradeStastics(VendorStatsticsDto vendor, StringBuffer sbNames, StringBuffer sbStatus) {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\ttvo.organization_name vendor_organization_name ,\n" +
                "\tcount( tog.id ) orderGoodNum,\n" +
                "\tsum( tog.amount ) orderAmount \n" +
                "FROM\n" +
                "\tordercenter.i_ordercenter_t_order_vendor_goods tog\n" +
                "\tLEFT JOIN ordercenter.i_ordercenter_t_order_vendor tvo ON tvo.id = tog.order_vendor_id \n" +
                "\tand tvo.deleted=0\n" +
                "WHERE\n" +
                "\ttog.deleted = 0 \n" );
        sb.append(" and tvo.order_vendor_status in ('" + sbStatus + "')");

        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and tvo.organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(vendor.getVendorOrganizationName())){
            sb.append(" and tvo.organization_name like '%" +vendor.getVendorOrganizationName() + "%'");
        }
        if (StringUtils.isNotBlank(vendor.getStartTime())){
            sb.append(" and tvo.createdTime >= '"+vendor.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(vendor.getEndTime())){
            sb.append(" and tvo.createdTime <= '"+vendor.getEndTime()+"'");
        }
        sb.append(" group by tvo.organization_name");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorOrderTradeStastics>(VendorOrderTradeStastics.class));
    }

    public static List<VendorOrderTradeReturnStastics> queryVendorOrderTradeReturnStastics(VendorStatsticsDto vendor, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\ttogr.vendor_organization_name ,\n" +
                "\tcount( togr.id ) refund_num,\n" +
                "\tsum( IFNULL(togr.vendor_amount,0)) refund_total \n" +
                "FROM\n" +
                "\tordercenter.i_ordercenter_t_order_goods_returns togr \n" +
                "WHERE\n" +
                "\ttogr.deleted = 0");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and togr.vendor_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(vendor.getVendorOrganizationName())){
            sb.append(" and togr.vendor_organization_name like '%" +vendor.getVendorOrganizationName() + "%'");
        }
        if (StringUtils.isNotBlank(vendor.getStartTime())){
            sb.append(" and togr.createdTime >= '"+vendor.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(vendor.getEndTime())){
            sb.append(" and togr.createdTime <= '"+vendor.getEndTime()+"'");
        }
        sb.append(" group by togr.vendor_organization_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorOrderTradeReturnStastics>(VendorOrderTradeReturnStastics.class));




    }

    public static List<VendorScoreQuotationDto> queryQuotationGoodScore(VendorStatsticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("select vendor_organization_id,");
        sb.append(" SUM(IF(bjsp.quotation_goods_status = '3' OR bjsp.quotation_goods_status = '4' OR bjsp.quotation_goods_status = '5' OR bjsp.quotation_goods_status = '6',xjsp.quote_count,\n" +
                "\t\t\t0 \n" +
                "\t\t)) AS bjcs,");
        sb.append("bjsp.vendor_organization_name");
        sb.append(" sum( xjsp.partake_count ) cycs,");
        sb.append(" sum(timestampdiff( MINUTE, xjsp.createdTime, bjsp.createdTime ))/ count(1)  bjsx");
        sb.append(" sum(IF( bjsp.picketage = 1, 1, 0 )) AS zbcs");
        sb.append(" FROM");
        sb.append(" sourcingcenter.i_sourcingcenter_t_quotation_goods bjsp");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods xjsp ON bjsp.inquiry_goods_id = xjsp.id");
        sb.append(" AND xjsp.deleted = 0");
        sb.append(" WHERE");
        sb.append(" bjsp.deleted = 0 and vendor_organization_id is not null");
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and bjsp.createdTime >= '"+good.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and bjsp.createdTime <= '"+good.getEndTime()+"'");
        }
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and bjsp.vendor_organization_name in ('" + sbNames + "')");
        }
        sb.append(" group by bjsp.vendor_organization_id;");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<VendorScoreQuotationDto>(VendorScoreQuotationDto.class));

    }

    public static List<InternalVendorQuotationVO> queryInnerVendorStatistics(VendorStatsticsDto vendor) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  \n" +
                "                data.organization_company_name as organizationCompanyName ,\n" +
                "                data.itime ,\n" +
                "                data.expire  ,\n" +
                "                data.inquiryId ,\n" +
                "                data.inquiryGoodsId  as id,\n" +
                "                data.id as quotationId ,\n" +
                "                data.name ,\n" +
                "                data.brand  ,\n" +
                "                data.spec  ,\n" +
                "                data.model  ,\n" +
                "                data.unit  ,\n" +
                "                data.num  ,\n" +
                "                data.time ,\n" +
                "                data.vendor_organization_name as vendorOrganizationName ,\n" +
                "                data.pretax_price  as  pretaxPrice,\n" +
                "                CASE \n" +
                "                WHEN data.STATUS = '0' THEN\n" +
                "                '已参与或参与未提交' \n" +
                "                WHEN data.STATUS = '1' THEN\n" +
                "                '审核中'\n" +
                "                WHEN data.STATUS = '2' THEN\n" +
                "                '已退回' \n" +
                "                WHEN data.STATUS = '3' THEN\n" +
                "                '已报价' \n" +
                "                WHEN data.STATUS = '4' THEN\n" +
                "                '已还价' \n" +
                "                WHEN data.STATUS = '5' THEN\n" +
                "                '已定标' \n" +
                "                WHEN data.STATUS = '6' THEN\n" +
                "                '已关闭' \n" +
                "                WHEN data.STATUS = '7' THEN\n" +
                "                '已终止' \n" +
                "                END AS status,\n" +
                "                data.winnbid ,\n" +
                "                data.placeorder  ,\n" +
                "                data.manager  , \n" +
                "                data.inquiryClarify  ,\n" +
                "                data.inquiryClarifyTime \n" +
                "                from (SELECT\n" +
                "\tiv.manager,\n" +
                "\tic.inquiryClarify,\n" +
                "\tic.inquiryClarifyTime,\n" +
                "\tqg.id,\n" +
                "\tqg.createdTime time,\n" +
                "\tqg.vendor_organization_name,\n" +
                "\tqg.vendor_organization_id,\n" +
                "\tqg.pretax_price,\n" +
                "\tqg.STATUS,\n" +
                "\ti.organization_company_name,\n" +
                "\ti.createdTime itime,\n" +
                "\ti.expire,\n" +
                "\ti.id inquiryId,\n" +
                "\ti.name inquiryName,\n" +
                "\tig.id inquiryGoodsId,\n" +
                "\tig.name,\n" +
                "\tig.brand,\n" +
                "\tig.spec,\n" +
                "\tig.model,\n" +
                "\tig.unit,\n" +
                "\tig.num,\n" +
                "\tig.quote_count,\n" +
                "CASE\n" +
                "\t\t\n" +
                "\t\tWHEN qg.STATUS = 5 THEN\n" +
                "\t\t'是' ELSE '否' \n" +
                "\tEND AS winnbid,\n" +
                "CASE\n" +
                "\t\t\n" +
                "\t\tWHEN gp.bought > 0 THEN\n" +
                "\t\t'是' ELSE '否' \n" +
                "\tEND AS placeorder \n" +
                "FROM\n" +
                "\t( SELECT * FROM sourcingcenter.i_sourcingcenter_t_quotation_goods WHERE deleted = FALSE AND virtual_vendor = TRUE ) qg\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\torganization_id,\n" +
                "\t\tGROUP_CONCAT( b.user_name ) AS manager \n" +
                "\tFROM\n" +
                "\t\toperation1center.i_operation1center_t_binding_invented_vendor b \n" +
                "\tWHERE\n" +
                "\t\tb.deleted = FALSE \n" +
                "\tGROUP BY\n" +
                "\t\tb.organization_id \n" +
                "\t) iv ON qg.vendor_organization_id = iv.organization_id\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods ig ON qg.inquiry_goods_id = ig.id\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry AS i ON i.id = ig.inquiry_id\n" +
                "\tLEFT JOIN commoditymaterials.i_commoditymaterials_t_goods_purchase gp ON gp.quotation_goods_id = qg.id\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tinquiry_goods_id,\n" +
                "\t\tvendor_id,\n" +
                "\t\tGROUP_CONCAT( clarify_tips ) AS inquiryClarify,\n" +
                "\t\tmin( createdTime ) AS inquiryClarifyTime \n" +
                "\tFROM\n" +
                "\t\tsourcingcenter.i_sourcingcenter_t_inquiry_goods_clarify \n" +
                "\tWHERE\n" +
                "\t\tdeleted = FALSE \n" +
                "\t\tAND vendor_id IS NOT NULL \n" +
                "\tGROUP BY\n" +
                "\t\tinquiry_goods_id,\n" +
                "\t\tvendor_id \n" +
                "\t) AS ic ON ic.inquiry_goods_id = qg.inquiry_goods_id \n" +
                "\tAND ( ic.vendor_id = qg.vendor_organization_id )");
        sb.append(" where 1=1");

        if (RegexUtils.notNull(vendor.getStartTime())) {
            sb.append( "  and  i.createdTime >= '" + vendor.getStartTime().trim() + "' ");
        }
        if (RegexUtils.notNull(vendor.getEndTime())) {
            sb.append( " and i.createdTime <= '" + vendor.getEndTime().trim() + "' ");
        }
        sb.append(") as data ORDER BY data.id DESC ;");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<InternalVendorQuotationVO>(InternalVendorQuotationVO.class));
    }

    public static List<Map<String, Object>> queryTradeAgreeQuotationData(VendorStatsticsDto vendor) {
        StringBuffer sb = new StringBuffer();
        sb.append("select qg.organization_company_name ,");
        sb.append(" qg.inquiry_time itime, ");//询价发布时间
        sb.append(" qg.expire, ");//询价截止时间
        sb.append("ig.inquiry_id inquiryId, ");//询价单号
        sb.append(" qg.inquiry_goods_id id, ");//询价商品id
        sb.append(" qg.brand, ");
        sb.append(" qg.name, ");
        sb.append(" qg.model, ");
        sb.append(" qg.spec, ");
        sb.append(" qg.pretax_price pretaxPrice, ");//税前单价
        sb.append(" qg.createdTime time time, ");//出价时间
        sb.append(" qg.vendor_organization_name vendorOrganizationName, ");//供应商
        sb.append(" qg.unit, ");//单位
        sb.append(" qg.num, ");//数量
        sb.append(" if(qg.quotation_goods_status = '5','是','否') winnbid ,");
        sb.append(" '' placeorder ,");
        sb.append(" '' mannger,");
        sb.append("  from sourcingcenter.i_sourcingcenter_t_quotation_goods qg left join systemmanagement.i_systemmanagement_t_user user on JSON_UNQUOTE(json_extract(qg.member_id,'$.id')) = user.id where user.protocol_account = 1 and user.deleted= 0 ");
        sb.append(" sourcingcenter.i_sourcingcenter_t_inquiry_goods ig on ig.id = qg.inquiry_goods_id and ig.deleted= 0 ");
        sb.append(" where 1=1 and qg.deleted = 0");
        if (RegexUtils.notNull(vendor.getStartTime())) {
            sb.append( "  and  qg.createdTime >= '" + vendor.getStartTime().trim() + "' ");
        }
        if (RegexUtils.notNull(vendor.getEndTime())) {
            sb.append( " and qg.createdTime <= '" + vendor.getEndTime().trim() + "' ");
        }
        List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sb.toString());
        return resultList;
    }
    public static List<Map<String, Object>> queryGoodPurchase(GoodTradeStatisticInfoDto good) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * ");
        sb.append("  from commoditymaterials.i_commoditymaterials_t_goods_purchase ");
        sb.append(" where 1=1 and deleted = 0");
        if (RegexUtils.notNull(good.getIds())) {
            sb.append (" and bjsp.id  in ( '"+ good.getIds()+"' )");
        }
        if (RegexUtils.notNull(good.getCreateStartTime())) {
            sb.append( " and  createdTime >= '" + good.getCreateStartTime().trim() + "' ");
        }
        if (RegexUtils.notNull(good.getCreateEndTime())) {
            sb.append( " and createdTime <= '" + good.getCreateEndTime().trim() + "' ");
        }
        if (RegexUtils.notNull(good.getVendor_name())) {
            sb.append (" and vendor_name like  '%"+ good.getVendor_name()+"%'");
        }
        if (RegexUtils.notNull(good.getModel())) {
            sb.append (" and model like  '%"+ good.getModel()+"%'");
        }
        if (RegexUtils.notNull(good.getBrand())) {
            sb.append (" and brand like  '%"+ good.getBrand()+"%'");
        }
        if (RegexUtils.notNull(good.getName())) {
            sb.append (" and name like  '%"+ good.getName()+"%'");
        }
        List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sb.toString());
        return resultList;
    }
}
